package cn.chinaflame.pagodas.service;
 

/**
 * new_edition商品行列service
 * @author  created by boming
 * @date    2018年5月7日 上午10:31:58
 */


import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Joiner;
import com.google.common.base.Splitter;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import cn.chinaflame.pagodas.Result.Result;
import cn.chinaflame.pagodas.constant.Sysconst;
import cn.chinaflame.pagodas.dto.PaProductPointDto;
import cn.chinaflame.pagodas.entity.PaOrder;
import cn.chinaflame.pagodas.entity.PaProductArea;
import cn.chinaflame.pagodas.entity.PaProductPoint;
import cn.chinaflame.pagodas.entity.PaProductPosition;
import cn.chinaflame.pagodas.enums.CodeEnum;
import cn.chinaflame.pagodas.pool.LightDispatcher;
import cn.chinaflame.pagodas.repository.PaOrderRepository;
import cn.chinaflame.pagodas.repository.PaOrderitemRepository;
import cn.chinaflame.pagodas.repository.PaProductAreaRepository;
import cn.chinaflame.pagodas.repository.PaProductPointRepository;
import cn.chinaflame.pagodas.repository.PaProductPositionRepository;
import cn.chinaflame.pagodas.req.PaProductPointReq;
import cn.chinaflame.pagodas.utils.DateTimes;
import cn.chinaflame.pagodas.utils.IDGen;
import cn.chinaflame.pagodas.utils.ParamPreconditions;
import cn.chinaflame.pagodas.utils.excel.ExcelParamDto;
import cn.chinaflame.pagodas.vo.LightVO;

/**
 * new_edition商品行列service
 * @author  created by boming
 * @date    2018年5月7日 上午10:31:58
 */
@Service
@EnableAsync
public class PaProductPointService {
	
	private static final String express="[0-9]+";//正则
	private static final String AREA="区";
	private static final String SEAT="01";//座号
	private static final String SEAT2="02";//座号
	private static final String PILLAR="墙";
	private static final String PILLAR1="柱";
	private static final Logger logger = LoggerFactory.getLogger(PaProductPointService.class);
	@Autowired
	private PaProductPointRepository paProductPointRepository;
	@Autowired
	private PaProductAreaRepository paProductAreaRepository;
	@Autowired
	private PaProductPositionRepository paProductPositionRepository;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private WrmNIService wrmNIService;
	@Autowired
	private CommonService commonService;
	public static String direction = "left";
	public static Integer type = 1;
	@Autowired
	private LightDispatcher lightDispatcher;
	@Autowired
	private PaOrderRepository paOrderRepository;
	@Autowired
	private PaOrderitemRepository paOrderitemRepository;
//	@Autowired
//	private DataSource dataSource;
//	  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	
	/**
	 * 查询摄像头位置信息列表
	 * @return
	 */
	public Result<List<PaProductPointDto>> queryList(PaProductPointReq req){
		logger.info("查询摄像头位置信息列表req:{}", req);
		List<PaProductPoint> positionList = paProductPointRepository.findAll(new Specification<PaProductPoint>() {
			@Override
			public Predicate toPredicate(Root<PaProductPoint> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> list = Lists.newArrayList();
				if(!Strings.isNullOrEmpty(req.getId()))
					list.add(cb.equal(root.get("id").as(String.class), req.getId()));
				if(!Strings.isNullOrEmpty(req.getPositionId()))
					list.add(cb.equal(root.get("positionId").as(String.class), req.getPositionId()));
				if(!Strings.isNullOrEmpty(req.getAreaId()))
					list.add(cb.equal(root.get("areaId").as(String.class), req.getAreaId()));
				if(!Strings.isNullOrEmpty(req.getRow()))
					list.add(cb.equal(root.get("row").as(String.class), req.getRow()));
				if(!Strings.isNullOrEmpty(req.getCol()))
					list.add(cb.equal(root.get("col").as(String.class), req.getCol()));
				if(!Strings.isNullOrEmpty(req.getLocationNo()))
					list.add(cb.equal(root.get("locationNo").as(String.class), req.getLocationNo()));
				if(!Objects.isNull(req.getVipFlg()))
					list.add(cb.equal(root.get("vipFlg").as(Integer.class), req.getVipFlg()));
				if(!Objects.isNull(req.getStatus())) {
					list.add(cb.equal(root.get("status").as(Integer.class), req.getStatus()));
				}
				Predicate[] p = new Predicate[list.size()];
				return cb.and(list.toArray(p));
			}
		});
		List<PaProductPointDto> dtoList = Lists.newArrayList();
		for(PaProductPoint point : positionList) {
			PaProductPointDto dto = new PaProductPointDto();
			BeanUtils.copyProperties(point, dto);
			PaProductArea ppa = paProductAreaRepository.getOne(point.getAreaId());
			PaProductPosition position = paProductPositionRepository.getOne(point.getPositionId());
			dto.setAreaName(ppa.getName());
			dto.setPositionName(position.getName());
			dtoList.add(dto);
		}
		return Result.create(dtoList);
	}
	/**
	 * 根据位置id获得库存
	 * @param positionId
	 * @return
	 */
	public int getStock(String positionId) {
		String sql = "select count(*) from pa_product_point where status="+Sysconst.POINT_STATUS_INSTALLED+" and position_id=?";
		Integer result = jdbcTemplate.queryForObject(sql, new Object[] {positionId}, Integer.class);
		return result == null ? 0 : result;
	}
	/**
	 * 根据位置id获得库存
	 * @param positionIds
	 * @return
	 */
	public int getStock2(String positionIds) {
		String sql = "select count(*) from pa_product_point where status="+Sysconst.POINT_STATUS_INSTALLED+" and FIND_IN_SET(position_id,?) > 0";
		Integer result = jdbcTemplate.queryForObject(sql, new Object[] {positionIds}, Integer.class);
		return result == null ? 0 : result;
	}
	/**
	 * 根据位置寻灯（左至右）
	 * @param positionId
	 * @return
	 */
	public PaProductPoint findLightByLeft(String positionId) {
		String sql = "select c.* from pa_product_point c where c.status="+Sysconst.POINT_STATUS_INSTALLED+
				" and c.position_Id="+positionId+" and c.vip_Flg=0 order by CAST(c.row AS SIGNED),CAST(c.col AS SIGNED) asc limit 0,1";
		JdbcQueryV2<PaProductPoint> query = new JdbcQueryV2<>(sql, jdbcTemplate, PaProductPoint.class);
		List<PaProductPoint> result = query.getContent();
		return result == null || result.size() == 0 ? null : result.get(0);
	}
	/**
	 * 点灯并调用点灯算法
	 * @param type
	 * @param positionId
	 * @return
	 */
	public PaProductPoint findLight(Integer type, String positionId){
		logger.info("type:{},positionId:{}",type,positionId);
		if(type ==1){
//			return findLightMaxPos(positionId);
			return findLightByRowCentral(positionId);
		}else if(type == 2){
			return findLightByLeft(positionId);
		}else{
			return findLightByRight(positionId);
		}
	}
	/**
	 * 按行从中间向两边寻灯
	 * @param positionId
	 * @return
	 */
	public PaProductPoint findLightByRowCentral(String positionId){
		logger.info("按行寻灯positionId:{}", positionId);
		int rows = paProductPointRepository.findByRowCountByPositionId(positionId);
		logger.info("总行数rows:{}", rows);
		if(rows <= 0) {
			return null;
		}
		int[] index = getIndex(rows);
		String order = "asc";
		boolean flg = false;
		if("left".equals(direction)){
			order = "asc";
		}else if("right".equals(direction)){
			order = "desc";
		}else {
			flg = true;
		}
		PaProductPoint point = null;
		for(int i=0;i<index.length;i++){
			String row = String.valueOf(index[i]);
			row = row.length() == 1 ? "0" + row : row;
			if(flg){
				order = i%2==0 ? "desc" : "asc";
			}
			String sql = "select c.* from pa_product_point c where c.row="+row+" and c.status="+Sysconst.POINT_STATUS_INSTALLED + 
					" and c.position_Id="+positionId+" and c.vip_Flg=0 order by CAST(c.col AS SIGNED) "+order+" limit 0,1";
			JdbcQueryV2<PaProductPoint> query = new JdbcQueryV2<>(sql, jdbcTemplate, PaProductPoint.class);
			List<PaProductPoint> result = query.getContent();
			if(result != null && result.size() > 0) {
				point = result.get(0);
				break;
			}
		}
		logger.info("按行寻灯结果positionId:{}", point);
		return point;
	}
	/**
	 * 按行从中间向两边寻灯
	 * @param positionId
	 * @return
	 */
	public PaProductPoint findLightMaxPos(String positionId){
		logger.info("按行寻灯positionId:{}", positionId);
		int rows = paProductPointRepository.findByRowCountByPositionId(positionId);
		logger.info("总行数rows:{}", rows);
		if(rows <= 0) return null;
		PaProductPoint point = null;
		for(Integer i=rows;i>0;i--){
			String row = i <10 ? "0" + i : i.toString();
			String sql = "select c.* from pa_product_point c where c.position_Id='"+positionId+"' and c.status="+Sysconst.POINT_STATUS_INSTALLED + 
					" and c.row="+row+" and c.vip_Flg=0 order by CAST(c.col AS SIGNED) desc limit 0,1";
			JdbcQueryV2<PaProductPoint> query = new JdbcQueryV2<>(sql, jdbcTemplate, PaProductPoint.class);
			List<PaProductPoint> result = query.getContent();
			if(result != null && result.size() > 0) {
				point = result.get(0);
				break;
			}
		}
		logger.info("按行寻灯结果positionId:{}", point);
		return point;
	}
	
	/**
	 * 
	 * @param count
	 * @return
	 */
	private int[] getIndex(int count){
		if(count < 1) {
			return null;
		}
		int[] result = new int[count];
		boolean isOdd = count%2 == 0 ? false : true;
		int k=0;
		if(isOdd){
			int ncount = count+1;
			for(int i=1;i<=count;i++){
				if(i % 2 == 0){
					k++;
					result[i-1] = ncount/2 - k;
				}else{
					result[i-1] = ncount/2 + k;
				}
			}
		}else{
			for(int i=1;i<=count;i++){
				if(i % 2 == 0){
					k++;
					result[i-1] = count/2 + k;
					
				}else{
					result[i-1] = count/2 - k;
				}
			}
		}
		return result;
	}
	/**
	 * 根据位置寻灯（右至左）
	 * @param positionId
	 * @return
	 */
	public PaProductPoint findLightByRight(String positionId) {
		logger.info("根据位置寻灯（右至左）positionId:{}",positionId);
		String sql = "select c.* from pa_product_point c where c.status="+Sysconst.POINT_STATUS_INSTALLED+
				" and c.position_Id="+positionId+" and c.vip_Flg=0 order by CAST(c.row AS SIGNED),CAST(c.col AS SIGNED) desc limit 0,1";
		JdbcQueryV2<PaProductPoint> query = new JdbcQueryV2<>(sql, jdbcTemplate, PaProductPoint.class);
		List<PaProductPoint> result = query.getContent();
		return result == null || result.size() == 0 ? null : result.get(0);
	}
	/**
	 * 下单寻灯
	 * @param positionId
	 */
	public PaProductPoint findLightByLeftByAddOrder(String positionId, String orderitemId) {
		logger.info("下单寻灯positionId:{}, orderitemId:{}", positionId, orderitemId);
		PaProductPoint point = findLight(type, positionId);
		ParamPreconditions.checkNotNull(point, CodeEnum.LIGHT_SEARCH_FAILED.getCode(), CodeEnum.LIGHT_SEARCH_FAILED.getMsg(), "未找到灯信息");
		paProductPointRepository.updateByStatusAndOrderitemIdByLocaNo(Sysconst.POINT_STATUS_LOCK, orderitemId, point.getLocationNo());
//		point.setStatus(Sysconst.POINT_STATUS_LOCK);
//		point.setOrderitemId(orderitemId);
//		paProductPointRepository.saveAndFlush(point);
		logger.info("findLightByLeftByAddOrder:point:{}",point);
		return point;
	}
	/**
	 * 支付成功寻灯
	 * @param beWishName
	 */
	public void findLightByLeftByPayOrder(String orderitemId,String beWishName) {
		logger.info("支付成功寻灯orderitemId:{},beWishName:{}", orderitemId,beWishName);
		paProductPointRepository.updateByStatusAndOrderitemId(Sysconst.POINT_STATUS_USING, orderitemId);
//		List<PaProductPoint> points = paProductPointRepository.findByOrderitemId(orderitemId);
//		ParamPreconditions.checkArgument(points != null && points.size() > 0, CodeEnum.FAIL.getCode(), "未找到灯信息");
//		for(PaProductPoint point : points) {
//			point.setStatus(Sysconst.POINT_STATUS_USING);
////			ParamPreconditions.checkArgument(Objects.equals(Sysconst.POINT_STATUS_LOCK, point.getStatus()), CodeEnum.FAIL.getCode(), "灯状态信息有误");
//			paProductPointRepository.saveAndFlush(point);
////			commonService.searchLight(Lists.newArrayList(point.getLocationNo()),beWishName);
//		}
		
	}
	/**
	 * 释放灯位置
	 * @param orderitemId
	 */
	public void findLightByLeftByRefundOrFinished(String orderitemId) {
		logger.info("释放灯位置orderitemId:{}", orderitemId);
		List<PaProductPoint> points = paProductPointRepository.findByOrderitemId(orderitemId);
		ParamPreconditions.checkArgument(points != null && points.size() > 0, CodeEnum.FAIL.getCode(), "未找到灯信息");
		for(PaProductPoint point : points) {
			point.setStatus(Sysconst.POINT_STATUS_INSTALLED);
			paProductPointRepository.saveAndFlush(point);
			commonService.clearLight(Lists.newArrayList(point.getLocationNo()));
		}
	}
	/**
	 * 寻灯闪烁
	 * @param orderitemId
	 * @return
	 */
	@Async
	public Object flash(String orderitemId, String beWishedName, String beWishedMemo){
		logger.info("寻灯闪烁orderitemId:{},beWishedName:{},beWishedMemo:{}", orderitemId,beWishedName,beWishedMemo);

		List<PaProductPoint> points = paProductPointRepository.findByOrderitemId(orderitemId);
		ParamPreconditions.checkArgument(points != null && points.size() > 0, CodeEnum.FAIL.getCode(), "未找到灯信息");
		List<Map<String, String>> param = Lists.newArrayList();
		StringBuilder sb = new StringBuilder();
		for(PaProductPoint m : points){
			sb.append(m.getLocationNo()).append(",");
			Map<String, String> p = Maps.newHashMap();
			p.put("lightno", m.getLocationNo());
			p.put("obj", beWishedName);
			p.put("bless", beWishedMemo);
			param.add(p);
		}
		String listvo = ParamPreconditions.toJson(param);
		logger.info("寻灯闪烁listvo:{}", sb.toString());
		List<LightVO> response = lightSearchByFlash(sb.toString());
		logger.info("闪烁结果response:{}", response);
		return Result.create("成功");
	}
	/**
	 * 批量清灯
	 * @return
	 */
	public boolean clearLight() {
		logger.info("清灯");
		String sql1 ="select product_point.* from pa_product_point product_point,pa_orderitem orderitem where product_point.orderitem_id=orderitem.id "+
				" and product_point.status = 4 and SUBSTR(orderitem.end_time,1,16) < SUBSTR(SYSDATE(),1,16)";
		JdbcQueryV2<PaProductPoint> jdbcQuery = new JdbcQueryV2<>(sql1, jdbcTemplate, PaProductPoint.class);
		List<PaProductPoint> result = jdbcQuery.getContent();
		if(result==null || result.isEmpty()==true){
			logger.info("暂没有需要清除的灯");
		return true;
		}
		
		List<String> locationNos = Lists.newArrayList();
		for(PaProductPoint point : result) {
			locationNos.add(point.getLocationNo());
			point.setStatus(Sysconst.POINT_STATUS_INSTALLED); //1位已安装 未使用
			point.setOrderitemId("F");
			point.setUpdatetime(DateTimes.nowDateTime());
		}
		try {
			paProductPointRepository.save(result);
		} catch (Exception e) {
			Result.create("ERROR", "PaProductPointService 批量清灯接口失败"+e.getMessage());
		}
		logger.info("清灯数据result:{}", locationNos);
		
		/*调远程接口清灯start*/
//		commonService.clearLight(locationNos);
		/*end*/
		return true;
	}
	
	/**
	 * 定时更新位置状态
	 */
	@Transactional()
	public void productPointStatusUpdate(){
		//-- 查询待点灯记录
		long start = System.currentTimeMillis();
		String light_sql = "select point.id,point.location_no,orderitem.be_wished_name,point.`status`,orderitem.start_time,orderitem.end_time,orderitem.be_wished_memo from pa_product_point point,pa_orderitem orderitem where point.orderitem_id=orderitem.id and point.`status`='4' "
			+ "and orderitem.start_time <= DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:00') and orderitem.end_time >= DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:59')";
		List<Map<String, Object>> light_res = jdbcTemplate.queryForList(light_sql);
		JSONArray param = new JSONArray();
		for(Map<String, Object> m : light_res){
			JSONObject  p = new JSONObject();
			p.put("lightno", m.get("location_no").toString());
			p.put("obj", m.get("be_wished_name").toString());
			p.put("bless", m.get("be_wished_memo").toString());
			param.add(p);
		}
		long end1 = System.currentTimeMillis();
		if(param != null && param.size() > 0){
			logger.info("查询待点灯记录耗时to_light:{}", (end1-start));
			logger.info("点灯操作light_res:{}", light_res);
			logger.info("###############test:{}", ParamPreconditions.toJson(param));
			List<LightVO> response = lightCheckAndSend(param);
			logger.info("点灯结果及耗时response:{},time:{}", response, (System.currentTimeMillis() - end1));
			String light_update_sql = "update pa_product_point point set point.num=0,point.`status`='5',point.updatetime=SYSDATE() where point.location_no=?";
//			String light_update_sql1 = "update pa_product_point point set point.num=point.num+1 where point.location_no=?";
			for(LightVO vo : response){
//				if(1 == vo.getResult()){
					jdbcTemplate.update(light_update_sql, vo.getLightno());
//				}
			}
		}
		//-- 查询待熄灯记录
		String clear_sql = "select point.id,point.location_no,orderitem.be_wished_name,point.`status`,orderitem.start_time,orderitem.end_time from pa_product_point point,pa_orderitem orderitem where point.`status` in(4,5) and point.orderitem_id=orderitem.id and orderitem.end_time < DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:00')";
		List<Map<String, Object>> clear_res = jdbcTemplate.queryForList(clear_sql);
		List<String> clear_param_list = Lists.newArrayList();
		for(Map<String, Object> m : clear_res){
			clear_param_list.add(m.get("location_no").toString());
		}
		String clear_param = Joiner.on(",").join(clear_param_list);
		if(!Strings.isNullOrEmpty(clear_param)){
			logger.info("远程熄灯clear_res:{},clear_param:{}", clear_res, clear_param);
			List<LightVO> response = lightClear(clear_param);
			//更改未支付灯过期的状态
			String clear_update_sql = "update pa_product_point point,pa_orderitem orderitem set point.`status`='1',point.orderitem_id=null,point.updatetime=SYSDATE() where point.`status` in(4,5) and point.orderitem_id=orderitem.id and orderitem.end_time < DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:00')";
			//更改支付后订单灯过期的状态
			String clear_update_sql1 = "update pa_product_point point,pa_orderitem orderitem,pa_order pa_order set point.`status`='1',point.orderitem_id=null,point.updatetime=SYSDATE() where pa_order.id=orderitem.order_id and pa_order.pay_status in(3,4,5) and point.orderitem_id=orderitem.id";
			jdbcTemplate.update(clear_update_sql);
			jdbcTemplate.update(clear_update_sql1);
			logger.info("远程熄灯返回结果result:{}", response);
			//-- 更新熄灯后记录
		}
		
	}
	public void dinshiXidengApi () {
		logger.info("进入定时熄灯接口");
		try{
			String sql = "SELECT ppp.id AS pointId,ppp.location_no,poi.id AS orderitemId " +
					"FROM pa_order po " +
					"INNER JOIN pa_orderitem poi ON po.id = poi.order_id " +
					"INNER JOIN pa_product_point ppp ON poi.location_no = ppp.location_no " +
					"WHERE po.pay_status = '2' AND poi.end_time < DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:00') AND poi.api_status = '2' AND ppp.`status` IN ('4','5')";
			List<Map<String, Object>> clear_res = jdbcTemplate.queryForList(sql);
			for(Map<String, Object> m : clear_res){
				LightVO lightVO = wrmNIService.wwtLightClear(m.get("location_no").toString());
				if (1 == lightVO.getResult()) {
					PaProductPoint paProductPoint =paProductPointRepository.findAllByPositionIdAndOrderitemIdAndLocationNo(
							m.get("pointId").toString(),m.get("orderitemId").toString(),m.get("location_no").toString());
					paProductPoint.setOrderitemId("");
					paProductPoint.setStatus("1");
					paProductPoint.setUpdatetime(DateTimes.nowDateTime());
					paProductPointRepository.saveAndFlush(paProductPoint);
				}

			}
		}catch (Exception e){
			e.printStackTrace();
		}
	}
	/**
	 * 改位置状态方法
	 */
	@Transactional()
	public void productPointStatusUpdateForTimeout(){
//		String expired_query_sql = "SELECT point.* FROM pa_product_point point,pa_orderitem orderitem WHERE point. STATUS IN (4, 5) AND point.orderitem_id = orderitem.id AND orderitem.end_time < DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:00')";
//		JdbcQueryV2<PaProductPoint> exipredQry = new JdbcQueryV2<>(expired_query_sql, jdbcTemplate, PaProductPoint.class);
//		List<PaProductPoint> points1 = exipredQry.getContent();
//		for(int i=0;i<points1.size();i++){
//			logger.info("过期状态修改pointId:{}", points1.get(i).getId());
//			points1.get(i).setStatus(Sysconst.POINT_STATUS_INSTALLED);
//			points1.get(i).setOrderitemId(null);
//			points1.get(i).setUpdatetime(DateTimes.nowDateTime());
//			paProductPointRepository.saveAndFlush(points1.get(i));
//		}
		String timeout_query_sql = "select point.* from pa_order pa_order,pa_orderitem orderitem, pa_product_point point where pa_order.id = orderitem.order_id and point.orderitem_id=orderitem.id and pa_order.pay_status in(3,4,5)";
		JdbcQueryV2<PaProductPoint> timeoutQry = new JdbcQueryV2<>(timeout_query_sql, jdbcTemplate, PaProductPoint.class);
		List<PaProductPoint> points = timeoutQry.getContent();
		for(int i=0;i<points.size();i++){
			logger.info("超时状态修改pointId:{}", points.get(i).getId());
			points.get(i).setStatus(Sysconst.POINT_STATUS_INSTALLED);
			points.get(i).setOrderitemId(null);
			points.get(i).setUpdatetime(DateTimes.nowDateTime());
			paProductPointRepository.saveAndFlush(points.get(i));
		}
	}
	/**
	 * 调用点灯寻灯接口
	 * @param lightvos
	 * @return
	 */
	public List<LightVO> lightCheckAndSend(JSONArray lightvos){
		logger.info("lightCheckAndSend:lightvos:{}",lightvos);
//		String response= HttpPostUtil.doPost2(productLightSearchAndSend, "lightvo="+lightvo);
//		wrmNIService.lighton();//先打开开关
//		return wrmNIService.lightCheckAndSend(lightvo);
		return lightDispatcher.lightSend(lightvos);
	}
	/**
	 * 批量清灯接口
	 * @param lightnos
	 * @return
	 */
	public List<LightVO> lightClear(String lightnos){
		logger.info("lightClear:lightnos:{}",lightnos);
		return lightDispatcher.lightClear(lightnos);
	}
	/**
	 * 寻灯，执行闪烁操作
	 * @param lightnos
	 */
	public List<LightVO> lightSearchByFlash(String lightnos){
		logger.info("lightSearchByFlash:lightnos:{}",lightnos);
		return lightDispatcher.lightSearch(lightnos);
	}
	/**
	 * 批量检查灯位接口
	 * @param lightnos
	 * @return
	 */
	public List<LightVO> lightCheck(String lightnos){
		logger.info("lightCheck:lightnos:{}",lightnos);
		return lightDispatcher.lightCheck(lightnos);
	}
	/**
	 * 把Excel灯号数据导入商品区域表，商品位置表，灯号表
	 * @param exceldtoList
	 * @param lights
	 */
	public void importExcel(List<ExcelParamDto> exceldtoList,List<String> lights) {
		logger.info("lights:{}",lights);
		logger.info("exceldtoList.size()="+exceldtoList.size());
		//循环取出对象
		for(ExcelParamDto exceldto:exceldtoList) {
			//判断对象不能为空，且对象所有字段不能为空
			if(exceldto != null && StringUtils.isNoneBlank(exceldto.getArea() ,exceldto.getSeat(),exceldto.getPillar(),exceldto.getLayer(),exceldto.getBits(),exceldto.getSumlights())
				//判断部分字段是否符合正则表达式
					&& exceldto.getArea().matches(express)&& exceldto.getSeat().matches(express)&& exceldto.getPillar().matches(express)&& exceldto.getLayer().matches(express)&& exceldto.getBits().matches(express)&& exceldto.getSumlights().matches(express)) {
				//添加到墙execl备份表
				String sql = "insert into excelparamdto(id,device_type,area,seat,pillar,layer,bits,device_sort,sumlights,createtime) value(?,?,?,?,?,?,?,?,?,now())";//插入Excel数据到镜像表
				jdbcTemplate.update(sql, new Object[] {IDGen.randomLong(),exceldto.getDeviceType(),exceldto.getArea(),exceldto.getSeat(),exceldto.getPillar(),exceldto.getLayer(),exceldto.getBits(),exceldto.getDeviceSort(),exceldto.getSumlights()});
		        //根据区名查询区域层信息
				String areasql = "select id from pa_product_area where name='"+exceldto.getArea()+AREA+"'";
		        logger.info(areasql);
		        String areaIdStr="";
				try {
					//执行查询区域层信息SQL
					List<String> areas = jdbcTemplate.queryForList(areasql, String.class);
					//判断list不为空就赋值"",不为空就赋值list里面的值
					areaIdStr = areas != null && areas.size()> 0 ? areas.get(0) :""	;
				} catch (Exception e1) {
					 logger.error(e1.getMessage());
				}
		        // String areaUpdateSql = "update pa_product_area set state=0,updatetime=now() where name='"+exceldto.getArea()+AREA+"'";
		        //jdbcTemplate.update(areaUpdateSql);
				//判断是否为空
				if(StringUtils.isBlank(areaIdStr)) {
					//获取区域层表ID
		        	String areaIdSql = "select nextid('yw_product_area') from DUAL";
		        	areaIdStr = jdbcTemplate.queryForObject(areaIdSql, String.class);
		        	//添加到区域表(层）
		        	String insql = "insert into pa_product_area(id,name,area_no,item_id,state,createtime,updatetime) values(?,?,?,'',0,now(),now())";
		        	jdbcTemplate.update(insql,new Object[] {areaIdStr,exceldto.getArea()+AREA,exceldto.getArea()+SEAT} );
		        }
		        //查询墙位置ID
		        String posquerySql = "select id from pa_product_position where name=? and position_no=? and area_id=?";
		        //更新墙位置ID
				String posUptsql = "update pa_product_position set state=0,updatetime=now() where name=? and position_no=? and area_id=?";
		        String positionId="";
				try {
					//获取墙位置ID
					positionId = jdbcTemplate.queryForObject(posquerySql, new Object[] {exceldto.getPillar()+(exceldto.getSeat().equals(SEAT)?PILLAR:PILLAR1),exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar(),areaIdStr},String.class);
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
				//更新墙位置ID
				jdbcTemplate.update(posUptsql, new Object[] {exceldto.getSeat().equals(SEAT)?PILLAR:PILLAR1,exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar(),areaIdStr});
				if(StringUtils.isBlank(positionId)) {
		        	String sqlStr = "select nextid('pa_product_position') from DUAL";
		        	String idStr =jdbcTemplate.queryForObject(sqlStr, String.class);
		        	String inssql="insert into pa_product_position(id,name,position_no,area_id,state,createtime,updatetime,device_type,device_sort,sumlights) values('"+idStr+"','"+(exceldto.getSeat().equals(SEAT)?PILLAR:PILLAR1)+"','"+(exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar())+"','"+areaIdStr+"','0',now(),now(),'"+exceldto.getDeviceType()+"','"+exceldto.getDeviceSort()+"','"+(exceldto.getSumlights() ==null?0:exceldto.getSumlights())+"')";
		        	jdbcTemplate.execute(inssql);
		        	logger.info(inssql);
		        	positionId = idStr;
		        }
		        int rows = Integer.parseInt(exceldto.getLayer());
		        int cols = Integer.parseInt(exceldto.getBits());
		        int count = 0;
		        logger.info("rows:{},cols{}",rows,cols);
		        for(int i=1;i<=rows;i++) {
		        	for(int j = 1;j<=cols;j++) {
		        		String lono = exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar()+(i<10?"0"+i:i)+(j<10?"0"+j:j);
		        		logger.info("lono:{}",lono);
			        	String pointQuerySql = "select id from pa_product_point where position_id=? and area_id=? and row=? and col=? and location_no=?";
			        	//String pointUptSql = "update pa_product_point set status=1,updatetime=now() where position_id=? and area_id=? and row=? and col=? and location_no=?";
			        	String point="";
						try {
							point = jdbcTemplate.queryForObject(pointQuerySql,new Object[] {positionId,areaIdStr,(i<10?"0"+i:i),(j<10?"0"+j:j),lono}, String.class);
						} catch (Exception e) {
							logger.error(e.getMessage());
						}
//			        	jdbcTemplate.update(pointUptSql,new Object[] {positionId,areaIdStr,(i<10?"0"+i:i),(j<10?"0"+j:j),lono});
						logger.info("StringUtils.isBlank(point)="+StringUtils.isEmpty(point)+"###"+(i<10?"0"+i:i)+"##"+(j<10?"0"+j:j)+"lono:{}",lono);
			        	if(StringUtils.isBlank(point)) {
			        		String pointsqlStr = "select nextid('pa_product_point') from DUAL";
			        		String pointidStr =jdbcTemplate.queryForObject(pointsqlStr,String.class);
			        		String inpossql="insert into pa_product_point(id,position_id,area_id,row,col,location_no,vip_flg,num,status,createtime,updatetime)values"
			        				+"('"+pointidStr+"','"+positionId+"','"+areaIdStr+"','"+(i<10?"0"+i:i)+"','"+(j<10?"0"+j:j)+"','"+lono+"',0,0,1,now(),now())";
			        		logger.info(inpossql);
			        		jdbcTemplate.execute(inpossql);
			        	}
			        	count++;
		        	}
		        }
		        logger.info("count:"+count);
			}
		}
		exculdeLights(lights);
	}
	
	/**
	 * 排除墙上没有的灯号
	 * @param lights
	 */
	public void exculdeLights(List<String> lights) {
		logger.info("lights.size()=",lights.size());
		String sql ="update pa_product_point set status=0 where location_no=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return lights.size();
			}
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				String lightno = lights.get(i);
				ps.setString(1, lightno);
			}
		  });
	}
	
	/**
	 * 异步修改祝福语
	 * @param orderitemId
	 * @param beWishedName
	 * @param beWishedMemo
	 */
	public void modifyBlessMemo(String orderitemId, String beWishedName, String beWishedMemo){
		logger.info("异步修改祝福语orderitemId:{},beWishedName:{},beWishedMemo:{}",orderitemId,beWishedName,beWishedMemo);
//		List<Map<String, String>> param = Lists.newArrayList();
		List<PaProductPoint> points = paProductPointRepository.findByOrderitemId(orderitemId);
		for(int i=0;i<points.size();i++){
			paProductPointRepository.updateByStatusAndOrderitemId(Sysconst.POINT_STATUS_USING, orderitemId);
//			if(Objects.equals(points.get(i).getStatus(), Sysconst.POINT_STATUS_LIGHTING)){
//				Map<String, String> p = Maps.newHashMap();
//				p.put("lightno", points.get(i).getLocationNo());
//				p.put("obj", beWishedName);
//				p.put("bless", beWishedMemo);
//				param.add(p);
//			}
		}
//		if(param.size() > 0){
//			List<LightVO> response = lightSearchAndSend(ParamPreconditions.toJson(param));
//			logger.info("点灯结果及耗时response:{}", response);
//		}
	}
	  public List<String> getLightsByArea(String areano)
	  {
	    logger.info("areano:{}", areano);
	    if (areano.length() == 1) {
	      areano = "0" + areano;
	    }
	    String areasql = "select location_no from pa_product_point where status=1 and position_id in(SELECT id from pa_product_position where position_no like '" + areano + "%')";
	    List result = this.jdbcTemplate.queryForList(areasql, String.class);

	    logger.info("result:{}", result);
	    return result;
	  }
	
	/**
	 * 获取某个区的所有灯号
	 * @param areano
	 * @return
	 */
	public List<String> genLightNos(String areano) {
		logger.info("areano:{}",areano);
//		String areasql = "select location_no from pa_product_point where status=1 and position_id in( SELECT id from pa_product_position where position_no like '0"+areano+"01%')";
		String areasql = "select location_no from pa_product_point where status=1 and position_id in( SELECT id from pa_product_position where position_no like '0202%')";
		List<String> result = jdbcTemplate.queryForList(areasql, String.class);
//		return String.join(",", result);	
		logger.info("result:{}",result);
		return result;
	}
	public List<String> genLightNos1(String areano) {
		logger.info("areano:{}",areano);
//		String areasql = "select location_no from pa_product_point where status=1 and position_id in( SELECT id from pa_product_position where position_no like '0"+areano+"01%')";
		String areasql = "select location_no from pa_product_point where status=1 and position_id in( SELECT id from pa_product_position where position_no like '0201%')";
		List<String> result = jdbcTemplate.queryForList(areasql, String.class);
//		return String.join(",", result);	
		logger.info("result:{}",result);
		return result;
	}
	/**
	 * 获取所有灯号
	 * @return
	 */
	public List<String> genLightNoAll() {
		String areasql = "select location_no from pa_product_point where status=1 and position_id in( SELECT id from pa_product_position where state=0 and area_id in (select id from pa_product_area where state=0))";
		List<String> result = jdbcTemplate.queryForList(areasql, String.class);
//		return String.join(",", result);	
		return result;
	}
	public String genLight() {
	    String sql = "select location_no from pa_product_point where location_no like '02020124%' and location_no > '0202012421'";
	    List<String> result = jdbcTemplate.queryForList(sql, String.class);
	    JSONArray param = new JSONArray();
	    for(int i=0;i<result.size();i++){
	    	JSONObject p = new JSONObject();
	        p.put("lightno", result.get(i));
	        p.put("obj", "上海炎武金融");
	        p.put("bless", "上海自来水来自海上");
	        param.add(p);
	    }
	    List<LightVO> response = lightCheckAndSend(param);
	    return JSONArray.toJSONString(response);
	}

	public Object queryAndPositionId (String positionId) {
		logger.info("商品位置根据墙号返回行和列service positionId:{}", positionId);
		try{
			List<PaProductPoint> positionList = paProductPointRepository.findAll(new Specification<PaProductPoint>() {
				@Override
				public Predicate toPredicate(Root<PaProductPoint> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
					List<Predicate> list = Lists.newArrayList();
					if(!Strings.isNullOrEmpty(positionId))
						list.add(cb.equal(root.get("positionId").as(String.class), positionId));
					Predicate[] p = new Predicate[list.size()];
					return cb.and(list.toArray(p));
				}
			});
			Map<String, String> max = paProductPointRepository.findMaxrowMaxcol(positionId);
			List<Map<String, String>> list = Lists.newArrayList();
			for (PaProductPoint paProductPoint:positionList){
				Map<String, String> map = Maps.newHashMap();
				map.put("Id",paProductPoint.getId());
				map.put("productId",paProductPoint.getProductId());
				map.put("row",paProductPoint.getRow());
				map.put("col",paProductPoint.getCol());
				map.put("locationNo",paProductPoint.getLocationNo());
				map.put("status",paProductPoint.getStatus());
				list.add(map);
			}
			Map<String, Object> restMap = Maps.newHashMap();
			restMap.put("row", max.get("row"));
			restMap.put("col", max.get("col"));
			restMap.put("list", list);
			return Result.create(restMap);
		}catch (Exception e) {
			logger.info("错误为 e:{}",e);
		}
		return Result.create("FALSE", "查询墙行和列信息失败");
	}

	public Object addByProductId (String data) {
		logger.info("商品绑定位置service data:{}",data);
		try{
			Gson gson = new Gson();
			List<PaProductPoint> list = gson.fromJson(data, new TypeToken<List<PaProductPoint>>(){}.getType());
			for (PaProductPoint req : list) {
				if(StringUtils.isEmpty(req.getProductId())){
					continue;
				}
				PaProductPoint paProductPoint = paProductPointRepository.findOne(req.getId());
				paProductPoint.setProductId(req.getProductId());
				paProductPointRepository.save(paProductPoint);
			}

			return Result.create("OK","添加成功");
		}catch (Exception e){
			logger.info("错误为 e:{}",e);
		}
		return Result.create("FALSE","添加失败");
	}

	public Object getPaProductPointByProductId (String productId,String orderitemId) {
		logger.info("根据商品编号查询对应位置service ProductId:{}",productId);
		if ("".equals(productId) || productId == null) {
			return Result.create("FALSE","商品编号不能为空");
		}
		PaProductPoint paProductPoint = null;
		try{
			String sql = "select * from pa_product_point where `status` = '1' AND product_id = '"+productId+"' order by area_id asc limit 0,1";
			paProductPoint = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(PaProductPoint.class));
			if(paProductPoint == null){
				return Result.create("FALSE","该商品暂无位置点灯");
			}
			int result = 0;
			StringBuffer locationnoStr = new StringBuffer();
			while (result == 1){
				LightVO lightVO = wrmNIService.wwtLightCheck(paProductPoint.getLocationNo());
				if ( 1 == lightVO.getResult()) {
					result = 1;
				}else{
					locationnoStr.append(paProductPoint.getLocationNo()).append(",");
					String sql1 = "select * from pa_product_point where `status` = '1' AND product_id = '"+productId+"' and location_no not in("+locationnoStr.substring(locationnoStr.length()-1).toString()+") order by area_id asc limit 0,1";
					paProductPoint = jdbcTemplate.queryForObject(sql1,new BeanPropertyRowMapper<>(PaProductPoint.class));
//					LightVO lightVO1 = wrmNIService.wwtLightCheck(paProductPoint.getLocationNo());
//					if (1 == lightVO1.getResult()) {
//						result = 1;
//					}else {
//
//					}
				}
			}
			paProductPointRepository.updateByStatusAndOrderitemIdByLocaNo(Sysconst.POINT_STATUS_LOCK, orderitemId, paProductPoint.getLocationNo());
			return Result.create(paProductPoint);
		}catch (Exception e){
			logger.info("错误为 e:{}",e);
		}
		return Result.create("FALSE","查询商品位置失败");
	}
}

